/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author MrKoi
 */
public class SalesAnalysisBean {
    Connect c=new Connect();
    Connection con;
    CallableStatement cl=null;
    public SalesAnalysisBean() {
    }
    private void closeCnn(){
        try {
            cl.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(AdminBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    //smallsize
    public String SelectSmallSize()
    {
        con=c.getConnection();
        String str="";
        try
        {
           cl=con.prepareCall("call SelectSmallSize_sp");
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               str=rs.getInt("Quantity")+"|"+rs.getFloat("TotalMoney");
           }
           closeCnn();
        }catch (Exception ex){closeCnn();}
        return str;
    }
    //fullsize
    public String SelectFullSize()
    {
        con=c.getConnection();
        String str="";
        try
        {
           cl=con.prepareCall("{call SelectFullSize_sp}");
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               str=rs.getInt("Quantity")+"|"+rs.getFloat("TotalMoney");
           }
           closeCnn();
        }catch (Exception ex){closeCnn();}
        return str;
    }
    //luxurye
    public String SelectLuxurye()
    {
       con=c.getConnection();
        String str="";
        try
        {
           cl=con.prepareCall("{call SelectLuxurye_sp}");
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               str=rs.getInt("Quantity")+"|"+rs.getFloat("TotalMoney");
           }
           closeCnn();
        }catch (Exception ex){closeCnn();}
        return str;
    }
    //minivan
    public String SelectMinivan()
    {
       con=c.getConnection();
        String str="";
        try
        {
           cl=con.prepareCall("{call SelectMinivan_sp}");
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               str=rs.getInt("Quantity")+"|"+rs.getFloat("TotalMoney");
           }
           closeCnn();
        }catch (Exception ex){closeCnn();}
        return str;
    }
    //childprice
     public String SelectChildprice()
    {
       con=c.getConnection();
        String str="";
        int songuoi=0;
        float tongtien=0;
        try
        {
           cl=con.prepareCall("{call SelectChildprice_sp}");
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               songuoi=songuoi + rs.getInt("TotalBook");
               tongtien=tongtien + rs.getFloat("TotalAdultPrice");
           }
           str=songuoi+"|"+tongtien;
           closeCnn();
        }catch (Exception ex){closeCnn();}
        return str;
    }
     //adultprice
      public String SelectAdultprice()
    {
       con=c.getConnection();
        String str="";
        int songuoi=0;
        float tongtien=0;
        try
        {
           cl=con.prepareCall("{call SelectAdultprice_sp}");
           ResultSet rs=cl.executeQuery();
           while(rs.next())
           {
               songuoi=songuoi + rs.getInt("TotalBook");
               tongtien=tongtien + rs.getFloat("TotalAdultPrice");
           }
           str=songuoi+"|"+tongtien;
           closeCnn();
        }catch (Exception ex){closeCnn();}
        return str;
    }
}
